﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Sql;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PowerPlant
{
    public class ExcelDriveManage
    {
        public static void ExportExcel(string strSqlTableName, string strSavePath)
        {
            DataTable sourceData = new DataTable();
            sourceData = DataManager.getDataTableByName(strSqlTableName);
            SaveAs(sourceData, strSavePath);
        }
        public static void ExportExcel(DataTable sourceData, string strSavePath)
        {
            SaveAs(sourceData, strSavePath);
        }
        public static void ExportExcel(DataGridView gridView,string strSavePath)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("1");
            dt.Columns.Add("2");
            dt.Columns.Add("3");
            dt.Columns.Add("4");
            dt.Columns.Add("5");
            foreach (DataGridViewRow item in gridView.Rows)
            {
                if (((DataGridViewCheckBoxCell)item.Cells[0]).Value != null && Convert.ToBoolean(((DataGridViewCheckBoxCell)item.Cells[0]).Value.ToString().ToLower()) == true)
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 1; i < item.Cells.Count; i++)
                    {
                        dr[i - 1] = item.Cells[i].Value;
                    }
                    dt.Rows.Add(dr);
                }
            }
            SaveAs(dt, strSavePath);
        }
        private static void SaveAs(DataTable sourceData,string strSavePath)
        {
            //导出到execl   
            try
            {
                if (strSavePath.Length != 0)
                {
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Application.Workbooks.Add(true); 
                    excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
                    sheet.Name = "test";
                    #region Old Implement
                    //int m = 0, n = 0;
                    //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
                    //for (int i = 1; i < gridView.ColumnCount; i++)
                    //{
                    //    excel.Cells[1, i] = gridView.Columns[i].HeaderText.ToString();
                    //}
                    ////填充数据
                    //for (int i = 0; i < gridView.RowCount; i++)
                    //{
                    //    //j也是从1开始  原因如上  每个人需求不一样
                    //    for (int j = 1; j < gridView.ColumnCount; j++)
                    //    {

                    //        if (gridView[j, i].Value.GetType() == typeof(string))
                    //        {
                    //            if (i < 9 && j == 1)
                    //            {
                    //                string s = "0" + gridView[j, i].Value.ToString().Trim();
                    //                excel.Cells[i + 2, j] = "'" + s;
                    //                continue;
                    //            }
                    //            excel.Cells[i + 2, j] = "'" + gridView[j, i].Value.ToString();
                    //        }
                    //        else
                    //        {
                    //            excel.Cells[i + 2, j] = gridView[j, i].Value.ToString();
                    //        }
                    //    }
                    //    //toolStripProgressBar1.Value += 100 / gridView.RowCount;
                    //}
                    #endregion
                    

                    for (int i = 0; i < sourceData.Rows.Count;i++ )
                    {
                        for (int j = 0; j < sourceData.Rows[i].ItemArray.Length; j++)
                        {
                            sheet.Cells[i+1,j+1] =sourceData.Rows[i].ItemArray[j].ToString();
                        }
                    }

                    sheet.SaveAs(strSavePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                    GC.Collect();
                    System.Diagnostics.Process process = new System.Diagnostics.Process();
                    process = System.Diagnostics.Process.GetProcessesByName("EXCEL")[0];
                    process.Kill();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }
        }

        private static DataTable ExcelDataSource(string filepath)
        {
            string strConn;
            string sheetname;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable(
                System.Data.OleDb.OleDbSchemaGuid.Tables,
                new object[] { null, null, null, "table" }
                );
            sheetname = sheetNames.Rows[0][2].ToString();
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
            DataTable dt = new DataTable();
            oada.Fill(dt);
            conn.Close();
            dt.Dispose();
            oada.Dispose();
            return dt;
        }

        public static bool ImportDataFromExcel(string filePath, string danjiName)
        {
            PowerPlantEntities PPE = new PowerPlantEntities();
            var model = PPE.danji.First(c => c.danji_name == danjiName);
            int danjiid = model.danji_id;
            //try
            //{
                string strCS =DataManager.getConnectionString();
                SqlConnection conn = new SqlConnection(strCS);
                conn.Open();
                DataTable dt = ExcelDataSource(filePath);
                foreach (DataRow dr in dt.Rows)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("insert into status_danji(status_date,status_fengsu,status_power,status_danjiId) values(");
                    for (int i = 0; i < dr.ItemArray.Length; i++)
                    {
                        sb.Append("'" + dr.ItemArray[i].ToString() + "'");
                        if (i != dr.ItemArray.Length - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    sb.Append(","+danjiid.ToString()+");");
                    string strCom = sb.ToString();
                    SqlCommand comm = new SqlCommand(strCom, conn);
                    comm.ExecuteNonQuery();
                }
                dt.Dispose();
                conn.Close();
                return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
    }
}
